﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.IO;
using System.Security.Cryptography;
using System.Text;

namespace Utility
{
    /// <summary>
    /// 数据访问抽象基础类
    /// Copyright (C) Maticsoft
    /// </summary>
    public class DbHelperSQL
    {
        private static DbHelper dbhelper = new DbHelper();

        public DbHelperSQL()
        {
        }

        #region 访问方式1

        /// <summary>
        /// 通用增加Model类 要求Model中所有公用字段在表中
        /// </summary>
        /// <param name="Item"></param>
        /// <returns></returns>
        public static int Insert(object Item, string TableName)
        {
            List<DbParameter> dbparam;
            string Sql;
            DBSqlHelper sqlhelper = new DBSqlHelper(dbhelper);
            sqlhelper.Insert(TableName, Item, out Sql, out dbparam);//构造 插入语句及参数
            sqlhelper.Dispose();
            return dbhelper.ExecuteNonQuery(Sql, dbparam);
        }

        /// <summary>
        /// 通用获得递增主键值 主键名称必在表中
        /// </summary>
        /// <param name="Item"></param>
        /// <returns></returns>
        public static int GetNewPrimaryKey(string TableName, string PrimaryKeyField)
        {
            int result = 1;
            string sql = "SELECT max(" + PrimaryKeyField + ") FROM " + TableName;
            object objvalue = dbhelper.ExecuteScaler(sql);
            if (objvalue != null)//当为新表时无值会反回null 所以判断一下
            {
                if (RegexCheck.RegexInt(objvalue.ToString()))//如果主键设置错误有可能返回字符串 所以进入整型判断
                {
                    result = int.Parse(objvalue.ToString()) + 1;
                }
            }
            return result;
        }

        /// <summary>
        /// 通用修改方法 要求以主键进行修改
        /// </summary>
        /// <param name="Item"></param>
        /// <returns></returns>
        public static int UPDATE(object Item, string TableName, string PrimaryKeyField)
        {
            List<DbParameter> dbparam;
            string Sql;
            DBSqlHelper sqlhelper = new DBSqlHelper(dbhelper);
            sqlhelper.UPDATE(TableName, Item, PrimaryKeyField, out Sql, out dbparam);//构造 UPDATE语句及参数
            sqlhelper.Dispose();
            return dbhelper.ExecuteNonQuery(Sql, dbparam);
        }

        /// <summary>
        /// whereStr 容易被注入 最好使用参数类型 (dbparamer)
        /// </summary>
        /// <param name="whereStr"></param>
        /// <returns></returns>
        public static int DELETE(string whereStr, string TableName)
        {
            string Sql = "DELETE FROM " + TableName;
            if (!string.IsNullOrEmpty(whereStr))
            {
                Sql += " WHERE " + whereStr;
            }

            return dbhelper.ExecuteNonQuery(Sql);
        }

        public static int DELETE(string whereStr, List<DbParameter> IistDbParameter, string TableName)
        {
            string Sql = "DELETE FROM " + TableName;
            if (!string.IsNullOrEmpty(whereStr))
            {
                Sql += " WHERE " + whereStr;
            }

            return dbhelper.ExecuteNonQuery(Sql, IistDbParameter);
        }

        public static DataTable GetTable(string Fields, string whereStr, string TableName)
        {
            string sql = "SELECT ";

            if (string.IsNullOrEmpty(Fields))
            {
                sql += " * ";
            }
            else
            {
                sql += Fields;
            }

            sql += " FROM " + TableName;
            if (!string.IsNullOrEmpty(whereStr))
            {
                sql += " WHERE " + whereStr;
            }

            return dbhelper.ExecuteDataTable(sql);
        }

        public static DataTable GetTable(string Feilds, string Wheres, string Orders, string TableName)
        {
            string sql = "SELECT ";
            if (!string.IsNullOrEmpty(Feilds))
            {
                sql += " " + Feilds + " ";
            }
            else
            {
                sql += " * ";
            }

            sql += " FROM " + TableName;
            if (!string.IsNullOrEmpty(Wheres))
            {
                sql += " WHERE " + Wheres;
            }
            if (!string.IsNullOrEmpty(Orders))
            {
                sql += " ORDER BY " + Orders;
            }

            return dbhelper.ExecuteDataTable(sql);
        }

        public static DataTable GetTable(string[] Fields, string whereStr, string TableName)
        {
            string sql = "SELECT ";
            bool Isfrist = true;
            if (Fields.Length > 0)
            {
                foreach (string str in Fields)
                {
                    if (!Isfrist)
                    {
                        sql += ",";
                    }
                    sql += str;
                    Isfrist = false;
                }
            }
            else
            {
                sql += " * ";
            }

            sql += " FROM " + TableName;
            if (!string.IsNullOrEmpty(whereStr))
            {
                sql += " WHERE " + whereStr;
            }

            return dbhelper.ExecuteDataTable(sql);
        }

        public static DataTable GetAllTable(string TableName)
        {
            return GetTable(null, TableName);
        }

        public static DataTable GetTable(string whereStr, string TableName)
        {
            string sql = "SELECT * FROM " + TableName;
            if (!string.IsNullOrEmpty(whereStr))
            {
                sql += " WHERE " + whereStr;
            }

            return dbhelper.ExecuteDataTable(sql);
        }

        public static DataTable GetTable(string whereStr, List<DbParameter> ListDbParameter, string TableName)
        {
            string sql = "SELECT * FROM " + TableName;
            if (!string.IsNullOrEmpty(whereStr))
            {
                sql += " WHERE " + whereStr;
            }

            return dbhelper.ExecuteDataTable(sql, ListDbParameter);
        }

        /// <summary>
        /// 通用分页功能
        /// </summary>
        /// <param name="Fields"> 查询字段</param>
        /// <param name="Tables"> 查询表</param>
        /// <param name="Wheres"> 查询条件</param>
        /// <param name="DbParas"> 带参数的条件</param>
        /// <param name="Orders">排序字段</param>
        /// <param name="PageSize">页面大小</param>
        /// <param name="PageIndex">第几页</param>
        /// <param name="PageCount">Out 总共分几页</param>
        /// <param name="RecordCount">Out 总记录数</param>
        /// <returns></returns>
        public static DataTable GetPageData(string Fields, string Tables, string Wheres, List<DbParameter> DbParas, string Orders, int PageSize, int PageIndex, out int PageCount, out int RecordCount)
        {
            DataTable result = null;
            int Page_Index = 1;
            int Page_Size = 20;

            string Sql_Count = "SELECT count(1) FROM " + Tables;
            if (PageIndex > 0)
            {
                Page_Index = PageIndex;
            }
            if (PageSize > 0)
            {
                Page_Size = PageSize;
            }
            StringBuilder SqlB = new StringBuilder("SELECT RS.* ,ROWNUM as rowno FROM( SELECT ");
            //查询字段
            if (string.IsNullOrEmpty(Fields))
            {
                SqlB.Append(" * ");
            }
            else
            {
                SqlB.Append(Fields);
            }
            SqlB.Append(" FROM ");
            //查询表
            if (string.IsNullOrEmpty(Tables))
            {
                throw new Exception("请指定需查询的表");
            }
            else
            {
                SqlB.Append(Tables);
            }
            //查询条件
            if (!string.IsNullOrEmpty(Wheres))
            {
                SqlB.Append(" WHERE " + Wheres);
                Sql_Count += " WHERE " + Wheres;
            }

            //开始统计
            RecordCount = int.Parse(dbhelper.ExecuteScaler(Sql_Count, DbParas).ToString());

            PageCount = RecordCount / Page_Size + (RecordCount % Page_Size > 0 ? 1 : 0);
            if (Page_Index > PageCount)
            {
                Page_Index = PageCount;
            }
            if (!string.IsNullOrEmpty(Orders))
            {
                SqlB.Append(" ORDER BY " + Orders);
            }
            SqlB.Append(" ) RS ");
            string Sql_Search = SqlB.ToString();

            if (Page_Index == 1)
            {
                Sql_Search = Sql_Search + " WHERE ROWNUM<=" + Page_Size;
            }
            else if (Page_Index > 1 && Page_Index < PageCount)
            {
                Sql_Search = "SELECT * FROM (" + Sql_Search + " WHERE ROWNUM<=" + Page_Size * Page_Index + ") RS2 WHERE RS2.ROWNO >" + (Page_Index - 1) * Page_Size;
            }
            else
            {
                Sql_Search = Sql_Search + " WHERE ROWNUM>" + (Page_Index - 1) * Page_Size;
            }
            result = dbhelper.ExecuteDataTable(Sql_Search, DbParas);
            return result;
        }

        #endregion 访问方式1

        #region 访问方式2

        #region 公用方法

        public static int GetMaxID(string FieldName, string TableName)
        {
            string strsql = "SELECT max(" + FieldName + ")+1 FROM " + TableName;
            object obj = GetSingle(strsql);
            if (obj == null)
            {
                return 1;
            }
            else
            {
                return int.Parse(obj.ToString());
            }
        }

        public static bool ColumnExists(string tableName, string columnName)
        {
            string sql = "SELECT count(1) FROM syscolumns WHERE [id]=object_id('" + tableName + "') AND [name]='" + columnName + "'";
            object res = GetSingle(sql);
            if (res == null)
            {
                return false;
            }
            return Convert.ToInt32(res) > 0;
        }

        public static bool TabExists(string TableName)
        {
            string strsql = "SELECT count(1) FROM sysobjects WHERE id = object_id(N'[" + TableName + "]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1";
            object obj = GetSingle(strsql);
            int cmdresult;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        public static bool Exists(string strSql)
        {
            object obj = GetSingle(strSql);
            int cmdresult;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString()); //也可能=0
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        public static bool Exists(string strSql, DbParameter[] cmdParms)
        {
            object obj = GetSingle(strSql, cmdParms);
            int cmdresult;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        #endregion 公用方法

        #region 执行简单SQL语句

        public static DataSet Query(string SQLString)
        {
            return dbhelper.ExecuteDataSet(SQLString);
        }

        public static int ExecuteSql(string SQLString)
        {
            return dbhelper.ExecuteNonQuery(SQLString);
        }

        public static object GetSingle(string SQLString)
        {
            return dbhelper.ExecuteScaler(SQLString);
        }

        public static object GetSingle(string SQLString, DbParameter[] cmdParms)
        {
            return dbhelper.ExecuteScaler(SQLString, cmdParms);
        }

        #endregion 执行简单SQL语句

        #region 返回DataTable

        public static DataTable GetDataTable(string SQLString)
        {
            return dbhelper.ExecuteDataTable(SQLString);
        }

        public static int getCount(string sql)
        {
            int count = 0;
            try
            {
                DataTable dt = GetDataTable(sql);
                if (int.Parse(dt.Rows[0][0].ToString()) > 0)
                {
                    count = int.Parse(dt.Rows[0][0].ToString());
                }
            }
            catch
            {
            }

            return count;
        }

        #endregion 返回DataTable

        #region 批量执行的方法

        public static bool ExecuteSqlTran(string SqlList)
        {
            return dbhelper.ExecuteSqlTran(SqlList);
        }

        public static bool ExecuteSqlTran(string SqlList, string operName)
        {
            return dbhelper.ExecuteSqlTran(SqlList, operName);
        }

        public static int ExecuteSqlTran(List<String> SQLStringList)
        {
            return dbhelper.ExecuteSqlTran(SQLStringList);
        }

        public static void ExecuteSqlTran(Hashtable SQLStringList)
        {
            dbhelper.ExecuteSqlTran(SQLStringList);
        }

        public static int ExecuteSql(string SQLString, string content)
        {
            return dbhelper.ExecuteSql(SQLString, content);
        }

        public static object ExecuteSqlGet(string SQLString, string content)
        {
            return dbhelper.ExecuteSqlGet(SQLString, content);
        }

        public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
        {
            return dbhelper.ExecuteSqlInsertImg(strSQL, fs);
        }

        #endregion 批量执行的方法

        #region 写日志

        public static int ExecuteSqlLog(string SQLString, string logonName)
        {
            writeLog(SQLString, logonName);
            return dbhelper.ExecuteNonQuery(SQLString);
        }

        public static void writeLog(string sql, string logonName)
        {
            string str = sql.Trim();
            string isSelect = str.Substring(0, 6);
            if (isSelect.ToUpper() == "SELECT")
            {
                //查询不写日志文件
            }
            else
            {
                //1.创建第一个大的文件夹  名字叫：ORA_LNG
                string firstFoler = "ORA_QFJS";
                string secondFoler = DateTime.Now.ToString("yyyy-MM-dd");
                if (!Directory.Exists("D:" + "\\" + firstFoler))
                {
                    //创建该文件夹
                    Directory.CreateDirectory("D:" + "\\" + firstFoler);
                    //创建二级文件夹
                    Directory.CreateDirectory("D:" + "\\" + firstFoler + "\\" + secondFoler);
                }
                else
                {
                    //2.创建第二级文件夹  以当天的日期命名
                    if (!Directory.Exists("D:" + "\\" + firstFoler + "\\" + secondFoler))
                    {
                        //创建
                        Directory.CreateDirectory("D:" + "\\" + firstFoler + "\\" + secondFoler);
                    }
                }

                string url = "D:" + "\\" + firstFoler + "\\" + secondFoler;

                string filename = url + "\\" + logonName + "_" + DateTime.Now.ToString("yyyyMMdd") + ".txt ";

                File.AppendAllText(filename, sql + ";\r\n");
            }
        }

        #endregion 写日志

        #region ========加密========

        /// <summary>
        /// 加密
        /// </summary>
        /// <param name="Text"></param>
        /// <returns></returns>
        public static string Encrypt(string Text)
        {
            return Encrypt(Text, "daijie");
        }

        /// <summary>
        /// 加密数据
        /// </summary>
        /// <param name="Text"></param>
        /// <param name="sKey"></param>
        /// <returns></returns>
        public static string Encrypt(string Text, string sKey)
        {
            DESCryptoServiceProvider des = new DESCryptoServiceProvider();
            byte[] inputByteArray;
            inputByteArray = Encoding.Default.GetBytes(Text);
            des.Key = ASCIIEncoding.ASCII.GetBytes(System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(sKey, "md5").Substring(0, 8));
            des.IV = ASCIIEncoding.ASCII.GetBytes(System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(sKey, "md5").Substring(0, 8));
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            CryptoStream cs = new CryptoStream(ms, des.CreateEncryptor(), CryptoStreamMode.Write);
            cs.Write(inputByteArray, 0, inputByteArray.Length);
            cs.FlushFinalBlock();
            StringBuilder ret = new StringBuilder();
            foreach (byte b in ms.ToArray())
            {
                ret.AppendFormat("{0:X2}", b);
            }
            return ret.ToString();
        }

        #endregion ========加密========

        #region ========解密========

        /// <summary>
        /// 解密
        /// </summary>
        /// <param name="Text"></param>
        /// <returns></returns>
        public static string Decrypt(string Text)
        {
            return Decrypt(Text, "daijie");
        }

        /// <summary>
        /// 解密数据
        /// </summary>
        /// <param name="Text"></param>
        /// <param name="sKey"></param>
        /// <returns></returns>
        public static string Decrypt(string Text, string sKey)
        {
            DESCryptoServiceProvider des = new DESCryptoServiceProvider();
            int len;
            len = Text.Length / 2;
            byte[] inputByteArray = new byte[len];
            int x, i;
            for (x = 0; x < len; x++)
            {
                i = Convert.ToInt32(Text.Substring(x * 2, 2), 16);
                inputByteArray[x] = (byte)i;
            }
            des.Key = ASCIIEncoding.ASCII.GetBytes(System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(sKey, "md5").Substring(0, 8));
            des.IV = ASCIIEncoding.ASCII.GetBytes(System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(sKey, "md5").Substring(0, 8));
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            CryptoStream cs = new CryptoStream(ms, des.CreateDecryptor(), CryptoStreamMode.Write);
            cs.Write(inputByteArray, 0, inputByteArray.Length);
            cs.FlushFinalBlock();
            return Encoding.Default.GetString(ms.ToArray());
        }

        #endregion ========解密========

        #endregion 访问方式2

        #region Dispose 成员

        public void Dispose()
        {
            GC.Collect();
        }

        ~DbHelperSQL()
        {
            Dispose();
        }

        #endregion Dispose 成员
    }
}